package de.reichelt.fxrequi.data;

import de.reichelt.fxrequi.Config;
import de.reichelt.fxrequi.FXrequi;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.control.TreeItem;

public class DataStore {

  private final int DB_VERSION = 0;
  
  private Connection conn;
  private User loginUser;
  
  private Logger log = null;
  private static DataStore ds = null;
  private static String DB_PATH = "./fxrequi";
  
  
  public static DataStore getInstance() throws SQLException {
    if (ds==null) {
      ds = new DataStore();
    }
    return ds;
  }
  
  public static void setDBPath(String path) {
    DB_PATH = path;
  }

  
  /**
   * Constructor reads the datastore configuration from propertyfile
   * 'config.properties' and connects to the configured database; if
   * database not available, this method creates the database and the tables.
   * 
   * (we use JavaDB (Apache Derby) in embedded mode)
   */
  private DataStore() throws SQLException {
    log = Logger.getLogger(this.getClass().getName());
    log.info("erzeuge DataStore-Instanz");
    String protocol = "";
    String dbName = "";
    Properties connProps = null;
    try {
      Config config = Config.getInstance();
      String driver = config.getString("db.driver");
      Class.forName(driver).newInstance();
      protocol = config.getString("db.protocol");
      connProps = new Properties(); // connection properties
      connProps.put("user", config.getString("db.user"));
      connProps.put("password", config.getString("db.passwd"));
      dbName = DB_PATH+"/"+config.getString("db.name"); // the name of the database
    } catch (Exception e) {
      throw new SQLException("Fehler beim Erzeugen der DatabaseConnection!", e);
    }
    // try to open an existing database; if not available create it
    try {
      String jdbcUrl = protocol + dbName;
      log.info("JDBC-URL=" + jdbcUrl);
      conn = DriverManager.getConnection(jdbcUrl, connProps);
      log.info("Datenbank '" + dbName + "' existiert bereits, gut so.");
      conn.setAutoCommit(false);
    } catch (Exception e) {
      log.info("Datenbank '"+dbName+"' existiert noch nicht, muss erzeugt werden.");
      createSchema(protocol, dbName, connProps);
      if (!FXrequi.PRODUCTION) {
        createTestdata();
      }
    }
    try {
      updateSchema();
    } catch (Exception e) {
      log.log(Level.SEVERE, "", e);
      throw new SQLException("Fehler beim Updaten des Datenbankschemas!", e);
    }    
  }
  
  private void createSchema(String protocol, String dbName, Properties connProps)
      throws SQLException {
    log.fine("aufgerufen");
    //Datei lesen
    List<String> sqls = new ArrayList<>();
    InputStream is = this.getClass().getResourceAsStream("/cr_anforderungen_derby.sql");
		BufferedReader in = new BufferedReader(new InputStreamReader(is));
	  try {
      String zeile = null;
      while ((zeile = in.readLine()) != null) {
			  sqls.add(zeile);
		  }
    } catch (Exception ex) {
      throw new RuntimeException("SQL-Datei konnte nicht gelesen werden!", ex);
    } finally {
      try {in.close();} catch (Exception ex) {}
    }
    //DB-Connection erzeugen
    conn = DriverManager.getConnection(protocol + dbName + ";create=true", connProps);
    conn.setAutoCommit(false);
    Statement st = conn.createStatement();
    String sql = "";
    try {
      for (String line: sqls) {
        String str = line.replaceFirst("--.*", "").trim();
        log.finer("str: "+str);
        sql = sql + str;
        if (str.endsWith(";")) {
          try {
            sql = sql.replace(";", "");
            log.fine("sql: "+sql);
            st.execute(sql);
          } catch (Exception ex) {
            log.log(Level.SEVERE, "Fehler beim Erzeugen der Tabellen!", ex);
          }
          sql = "";
        }
      }
      conn.commit();
      log.finer("Transaktionen committed");
    } finally {
      if (st != null) {
        st.close();
      }
    }
  }

  private void createTestdata() throws SQLException {
    //Datei lesen
    List<String> sqls = new ArrayList<>();
		BufferedReader in = new BufferedReader(new InputStreamReader(this.getClass().getResourceAsStream("/insert_testdata.sql")));
	  try {
      String zeile = null;
      while ((zeile = in.readLine()) != null) {
			  sqls.add(zeile);
		  }
    } catch (Exception ex) {
      throw new RuntimeException("SQL-Datei konnte nicht gelesen werden!", ex);
    } finally {
      try {in.close();} catch (Exception ex) {}
    }
    Statement st = conn.createStatement();
    String sql = "";
    try {
      for (String line: sqls) {
        String str = line.replaceFirst("--.*", "").trim();
        log.finer("str: "+str);
        sql = sql + str;
        if (str.endsWith(";")) {
          try {
            sql = sql.replace(";", "");
            log.fine("sql: "+sql);
            st.execute(sql);
          } catch (Exception ex) {
            log.log(Level.SEVERE, "Fehler beim Erzeugen der Tabellen!", ex);
          }
          sql = "";
        }
      }
      conn.commit();
      log.finer("Transaktionen committed");
    } finally {
      if (st != null) {
        st.close();
      }
    }
  }

  private void updateSchema() throws Exception {
    /*
     * aktualisiert die Datenstrukturen der DB. Dazu muss die vom Programm
     * geforderte DB_VERSION als Konstante in dieser Klasse existieren. Die
     * aktuelle Version der Datenbank steht in der DB-Tabelle 'version'. Wenn
     * diese nicht der geforderten DB-Version entspricht, werden die
     * entsprechenden DBUpdater-Klassen aufgerufen. Diese werden dynamisch nach
     * dem Namensmuster 'jpf.data.DBUpdater_{version}' geladen und müssen vom
     * Interface DBUpdater abgeleitet sein.
     */
    log.finer("aufgerufen, geforderte dbVersion=" + DB_VERSION);
    Statement stmt = conn.createStatement();
    ResultSet rs = null;
    int actualVersion = 0;
    boolean severeProblem = false;
    try {
      rs = stmt.executeQuery("select version from version");
      if (rs.next()) {
        actualVersion = rs.getInt(1);
      } else {
        log.severe("Tabelle 'version' existiert, enthaelt jedoch keine Daten!");
        severeProblem = true;
      }
    } catch (SQLException ex) {
      log.finer("Tabelle 'version' existiert noch nicht");
      actualVersion = 0;
    } finally {
      if (rs != null) {
        rs.close();
      }
      stmt.close();
    }
    if (severeProblem) {
      throw new SQLException("Fehler bei Pruefung der DB-Version!");
    }

    if (DB_VERSION > 0) {
      for (int i = actualVersion + 1; i <= DB_VERSION; i++) {
        DBUpdater updater = (DBUpdater) (Class.forName("jpf.data.DBUpdater_"
            + Integer.toString(i)).newInstance());
        updater.updateDB(conn);
      }
    }

    log.finer("beendet");
  }
  
  public User getLoginUser() {
    return this.loginUser;
  }
  
  public void setLoginUser(User user) {
    this.loginUser = user;
  }

  public void closeDB() throws SQLException {
    log.finer("schliesse Datenbank");
    conn.commit();
    conn.close();
    // DriverManager.getConnection("jdbc:derby:;shutdown=true");
  }

  public void commit() throws SQLException {
    log.finer("committe Änderungen");
    conn.commit();
  }

  public void rollback() throws SQLException {
    log.finer("widerrufe Änderungen");
    conn.rollback();
  }

  public List<Project> getProjects() throws SQLException {
    log.finer("aufgerufen");
    List<Project> list = new ArrayList<>();
    Statement stmt = null;
    try {
      stmt = conn.createStatement();
      String sql = "select name, title, state, descr, date_created, user_created, keywords "
              + "from projects where name!='STANDARD' order by name";
      log.finest("sql="+sql);
      ResultSet rs = stmt.executeQuery(sql);
      Project obj;
      while (rs.next()) {
        obj = new Project(rs.getString("name"), rs.getString("title"),
                  Project.getState(rs.getString("state")), rs.getString("descr"),
                  rs.getDate("date_created"), rs.getString("user_created"), 
                  rs.getString("keywords")  );
        list.add(obj);
      }
    } finally {
      if (stmt != null) {stmt.close();}
    }
    log.finer("beendet");
    return list;
  }

  public Project getProjectById(String name) throws SQLException {
    log.finer("aufgerufen");
    Project obj = null;
    PreparedStatement ps = null;
    try {
      String sql = "select name, title, state, descr, date_created, user_created, keywords "
              + "from projects where name=?";
      log.finest("sql="+sql);
      ps = conn.prepareStatement(sql);
      ps.setString(1, name);
      ResultSet rs = ps.executeQuery();
      if (rs.next()) {
        obj = new Project(rs.getString("name"), rs.getString("title"),
                  Project.getState(rs.getString("state")), rs.getString("descr"),
                  rs.getDate("date_created"), rs.getString("user_created"), 
                  rs.getString("keywords") );
      }
    } finally {
      if (ps != null) {ps.close();}
    }
    log.finer("beendet");
    return obj;
  }

  public void insertProject(Project obj) throws SQLException {
    log.finer("aufgerufen");
    log.finest("Daten: "+obj);
    String sql = "insert into projects (name, title, state, descr, date_created, user_created, keywords) "
            + "values (?, ?, ?, ?, ?, ?, ?)";
    log.finest("sql="+sql);
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, obj.getName());
      stmt.setString(2, obj.getTitle());
      stmt.setString(3, obj.getState().getKey());
      stmt.setString(4, obj.getDescr());
      stmt.setDate(5, new java.sql.Date(new Date().getTime()));
      stmt.setString(6, obj.getUserCreated());
      stmt.setString(7, obj.getKeywords());
      stmt.executeUpdate();
      conn.commit();
      log.finer("Anzahl eingefuegt=" + stmt.getUpdateCount());
    } finally {
      if (stmt!=null) {stmt.close();}
    }
    log.finer("beendet");
  }

  public void updateProject(Project obj) throws SQLException {
    log.finer("aufgerufen");
    String sql = "update projects set title=?, state=?, descr=?, keywords=? where name=?";
    log.finest("SQL="+sql+"\nParameter: name="+obj.getName()+"title="+obj.getTitle()+
        ", state="+obj.getState()+", descr="+obj.getDescr()+", keywords="+obj.getKeywords());
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, obj.getTitle());
      stmt.setString(2, obj.getState().getKey());
      stmt.setString(3, obj.getDescr());
      stmt.setString(4, obj.getKeywords());
      stmt.setString(5, obj.getName());
      stmt.executeUpdate();
      conn.commit();
      log.finer("Anzahl upgedated=" + stmt.getUpdateCount());
    } finally {
      if (stmt!=null) {stmt.close();}
    }
    log.finer("beendet");
  }

  public void deleteProject(String name) throws SQLException {
    log.finer("aufgerufen");
    String sql = "delete from projects where name=?";
    log.finest("SQL="+sql+"\nParameter: name="+name);
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, name);
      stmt.executeUpdate();
      conn.commit();
      log.finer("Anzahl geloescht="+stmt.getUpdateCount());
    } finally {
      if (stmt!=null) {stmt.close();}
    }
    log.finer("beendet");
  }

  /**
   * liest alle Anforderungen eines Projekts. Zu jeder Anforderung werden die 
   * Unteranforderungen rekursiv gelesen. Wenn nur die projectId angegeben ist,
   * wird die erste Ebene gelesen. Wenn zusätzlich die reqId angegeben ist, werden 
   * die Unteranforderungen dieser Anforderung gelesen.
   * 
   * @param item  übergeordneter Baumknoten, in den die weiteren Baumknoten eingehängt werden
   * @param projectName  Kennung des Projekts
   * @param reqId  Id der übergeordneten Anforderung
   * @return 
   */
  public void getRequirementsTree(TreeItem<Requirement> item, String projectName, Integer reqId) throws SQLException {
    log.finer("aufgerufen: projectName="+projectName+", reqId="+reqId);
    TreeItem<Requirement> ti = new TreeItem<>();
    PreparedStatement st = null;
    try {
      String sql = "select id, project_name, version_name, artefact_id, parent_id, "
              + "counter, title, descr, priority, state, stakeholder, stakeholder_type, "
              + "category, remark, test_required, worker, work_state, work_comment, "
              + "solution, costs, keywords, date_created, user_created "
              + "from requirements "
              + "where project_name=? and parent_id ### "
              + "order by counter";
      if (reqId==null) {
        sql = sql.replace("###", "is null");
        st = conn.prepareStatement(sql);
        st.setString(1, projectName);
      } else {
        sql = sql.replace("###", "=?");
        st = conn.prepareStatement(sql);
        st.setString(1, projectName);
        st.setInt(2, reqId);
      }
      log.finest("sql="+sql);
      ResultSet rs = st.executeQuery();
      while (rs.next()) {
        Requirement req = new Requirement();
        req.setId(rs.getInt("id"));
        req.setProjectName(rs.getString("project_name"));
        req.setProjectVersion(getProjectVersionByName(projectName, rs.getString("version_name")));
        req.setArtefact(getArtefact(rs.getInt("artefact_id")));
        req.setParentId(rs.getInt("parent_id"));
        req.setCounter(rs.getString("counter"));
        req.setTitle(rs.getString("title"));
        req.setDescr(rs.getString("descr"));
        req.setPriority(Requirement.getPriority(rs.getString("priority")));
        req.setState(Requirement.getState(rs.getString("state")));
        req.setStakeholder(rs.getString("stakeholder"));
        req.setStakeholderType(Requirement.getStakeholderType(rs.getString("stakeholder_type")));
        req.setCategory(getCategory(rs.getString("category")));
        req.setRemark(rs.getString("remark"));
        boolean testRequired = (rs.getString("test_required")!=null && 
                rs.getString("test_required").equals("J"));
        req.setTestRequired(testRequired);
        req.setWorker(rs.getString("worker"));
        req.setWorkState(Requirement.getWorkState(rs.getString("work_state")));
        req.setWorkComment(rs.getString("work_comment"));
        req.setSolution(rs.getString("solution"));
        if (rs.getString("costs")!=null && !rs.getString("costs").isEmpty()) {
          req.setCosts(Integer.valueOf(rs.getString("costs")));
        }
        req.setKeywords(rs.getString("keywords"));
        req.setUserCreated(rs.getString("user_created"));
        req.setDateCreated(rs.getDate("date_created"));
        TreeItem<Requirement> it = new TreeItem<>(req);
        item.getChildren().add(it);
        getRequirementsTree(it, projectName, rs.getInt("id"));
      }
    } finally {
      if (st != null) {st.close();}
    }
    log.finer("beendet");
  }

  public ProjectX getProjectXById(String name) throws SQLException {
    log.finer("aufgerufen");
    ProjectX pro = null;
    PreparedStatement ps = null;
    try {
      String sql = "select name, title, state, descr, keywords "
              + "from projects where name=?";
      log.finest("sql="+sql);
      ps = conn.prepareStatement(sql);
      ps.setString(1, name);
      ResultSet rs = ps.executeQuery();
      if (rs.next()) {
        pro = new ProjectX(rs.getString("name"), rs.getString("title"),
                  rs.getString("state"), rs.getString("descr"),
                  rs.getString("keywords") );
      }
      pro.setRequirements(getRequirementXSubList(name, null));
      pro.setProjectVersions(getVersionList(getVersionsByProjectName(name)));
      pro.setArtefacts(getArtefactList(getArtefactsByProjectName(name)));
      pro.setGlossary(getGlossaryList(getGlossaryByProjectName(name)));
    } finally {
      if (ps != null) {ps.close();}
    }
    log.finer("beendet");
    return pro;
  }

  public List<RequirementX> getRequirementXSubList(String projectName, Integer reqId) throws SQLException {
    log.finer("aufgerufen: projectName="+projectName+", reqId="+reqId);
    List<RequirementX> list = new ArrayList<>();
    PreparedStatement st = null;
    try {
      String sql = "select r.id, r.project_name, r.version_name, a.title as atitle, parent_id, "
              + "counter, r.title as rtitle, descr, priority, state, stakeholder, stakeholder_type, "
              + "category, remark, test_required, worker, work_state, work_comment, "
              + "solution, costs, keywords "
              + "from requirements r "
              + "LEFT OUTER JOIN artefacts a ON a.id = r.artefact_id "
              + "where r.project_name=? and parent_id ### "
              + "order by counter";
      if (reqId==null) {
        sql = sql.replace("###", "is null");
        st = conn.prepareStatement(sql);
        st.setString(1, projectName);
      } else {
        sql = sql.replace("###", "=?");
        st = conn.prepareStatement(sql);
        st.setString(1, projectName);
        st.setInt(2, reqId);
      }
      log.finest("sql="+sql);
      ResultSet rs = st.executeQuery();
      while (rs.next()) {
        RequirementX req = new RequirementX();
        int thisId = rs.getInt("id");
        req.setProjectVersion(rs.getString("version_name"));
        req.setArtefact(rs.getString("atitle"));
        req.setCounter(rs.getString("counter"));
        req.setTitle(rs.getString("rtitle"));
        req.setDescr(rs.getString("descr"));
        req.setPriority(rs.getString("priority"));
        req.setState(rs.getString("state"));
        req.setStakeholder(rs.getString("stakeholder"));
        req.setStakeholderType(rs.getString("stakeholder_type"));
        req.setCategory(rs.getString("category"));
        req.setRemark(rs.getString("remark"));
        boolean testRequired = (rs.getString("test_required")!=null && 
                rs.getString("test_required").equals("J"));
        req.setTestRequired(testRequired);
        req.setWorker(rs.getString("worker"));
        req.setWorkState(rs.getString("work_state"));
        req.setWorkComment(rs.getString("work_comment"));
        req.setSolution(rs.getString("solution"));
        if (rs.getString("costs")!=null && !rs.getString("costs").isEmpty()) {
          req.setCosts(Integer.valueOf(rs.getString("costs")));
        }
        req.setKeywords(rs.getString("keywords"));
        req.setRequirements(getRequirementXSubList(projectName, thisId));
        list.add(req);
      }
      log.finer("beendet");
      return list;
    } finally {
      if (st != null) {st.close();}
    }
  }

  public void insertRequirement(Requirement req) throws SQLException {
    log.finer("aufgerufen");
    log.finest("Daten: "+req);
    String sql = "insert into requirements "
            + "(project_name, parent_id, counter, title, descr, remark, "
            + " priority, state, version_name, artefact_id, category, test_required, "
            + " stakeholder, stakeholder_type, worker, work_state, work_comment, "
            + " solution, costs, keywords, date_created, user_created) "
            + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    log.finest("sql="+sql);
    PreparedStatement st = null;
    try {
      st = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
      st.setString(1, req.getProjectName());
      if (req.getParentId()==null || req.getParentId()==0) st.setNull(2, Types.INTEGER); else st.setInt(2, req.getParentId());
      st.setString(3, req.getCounter());
      st.setString(4, req.getTitle());
      st.setString(5, req.getDescr());
      st.setString(6, req.getRemark());
      
      if (req.getPriority()==null) st.setNull(7, Types.VARCHAR); else st.setString(7, req.getPriority().getKey());
      if (req.getState()==null) st.setNull(8, Types.VARCHAR); else st.setString(8, req.getState().getKey());
      if (req.getProjectVersion()==null) st.setNull(9, Types.VARCHAR); else st.setString(9, req.getProjectVersion().getName());
      if (req.getArtefact()==null) st.setNull(10, Types.VARCHAR); else st.setInt(10, req.getArtefact().getId());
      if (req.getCategory()==null) st.setNull(11, Types.VARCHAR); else st.setString(11, req.getCategory().getName());
      st.setString(12, req.isTestRequired()?"J":"N");
      
      st.setString(13, req.getStakeholder());
      if (req.getStakeholderType()==null) st.setNull(14, Types.VARCHAR); else st.setString(14, req.getStakeholderType().getKey());
      st.setString(15, req.getWorker());
      if (req.getWorkState()==null) st.setNull(16, Types.VARCHAR); else st.setString(16, req.getWorkState().getKey());
      st.setString(17, req.getWorkComment());
      
      st.setString(18, req.getSolution());
      st.setInt(19, req.getCosts());
      st.setString(20, req.getKeywords());
      st.setDate(21, new java.sql.Date(req.getDateCreated().getTime()));
      st.setString(22, req.getUserCreated());
      st.executeUpdate();
      ResultSet rs = st.getGeneratedKeys();
      rs.next();
      req.setId(rs.getInt(1));
      conn.commit();
      log.finer("Datensatz inserted");
    } finally {
      if (st!=null) {st.close();}
    }
  }
  
  public void updateRequirement(Requirement req) throws SQLException {
    log.finer("aufgerufen");
    log.finest("Daten: "+req);
    String sql = "update requirements set "
            + "parent_id=?, counter=?, title=?, descr=?, remark=?, "
            + "priority=?, state=?, version_name=?, artefact_id=?, category=?, test_required=?, "
            + "stakeholder=?, stakeholder_type=?, worker=?, work_state=?, work_comment=?, "
            + "solution=?, costs=?, keywords=? "
            + "where id=?";
    log.finest("sql="+sql);
    PreparedStatement st = null;
    try {
      st = conn.prepareStatement(sql);
      if (req.getParentId()==null || req.getParentId()==0) st.setNull(1, Types.INTEGER); else st.setInt(1, req.getParentId());
      st.setString(2, req.getCounter());
      st.setString(3, req.getTitle());
      st.setString(4, req.getDescr());
      st.setString(5, req.getRemark());
      
      if (req.getPriority()==null) st.setNull(6, Types.VARCHAR); else st.setString(6, req.getPriority().getKey());
      if (req.getState()==null) st.setNull(7, Types.VARCHAR); else st.setString(7, req.getState().getKey());
      if (req.getProjectVersion()==null) st.setNull(8, Types.VARCHAR); else st.setString(8, req.getProjectVersion().getName());
      if (req.getArtefact()==null) st.setNull(9, Types.VARCHAR); else st.setInt(9, req.getArtefact().getId());
      if (req.getCategory()==null) st.setNull(10, Types.VARCHAR); else st.setString(10, req.getCategory().getName());
      st.setString(11, req.isTestRequired()?"J":"N");
      
      st.setString(12, req.getStakeholder());
      if (req.getStakeholderType()==null) st.setNull(13, Types.VARCHAR); else st.setString(13, req.getStakeholderType().getKey());
      st.setString(14, req.getWorker());
      if (req.getWorkState()==null) st.setNull(15, Types.VARCHAR); else st.setString(15, req.getWorkState().getKey());
      st.setString(16, req.getWorkComment());
      
      st.setString(17, req.getSolution());
      st.setInt(18, req.getCosts());
      st.setString(19, req.getKeywords());
      st.setInt(20, req.getId());
      st.executeUpdate();
      conn.commit();
      log.finer("Datensatz updated");
    } finally {
      if (st!=null) {st.close();}
    }
  }
  
  public void deleteRequirement(int id) throws SQLException {
    log.finer("aufgerufen: id="+id);
    String sql = "delete from requirements where id=?";
    log.finest("sql="+sql);
    PreparedStatement st = null;
    try {
      st = conn.prepareStatement(sql);
      st.setInt(1, id);
      st.executeUpdate();
      conn.commit();
      log.finer("Datensatz deleted");
    } finally {
      if (st!=null) {st.close();}
    }
  }
  
  public ObservableList<ProjectVersion> getVersionsByProjectName(String projectName) throws SQLException {
    log.finer("aufgerufen: projectName="+projectName);
    List<ProjectVersion> list = new ArrayList<>();
    PreparedStatement st = null;
    try {
      String sql = "select id, name, descr, date_created, user_created "
              + "from project_versions "
              + "where project_name=? "
              + "order by name ";
      st = conn.prepareStatement(sql);
      st.setString(1, projectName);
      log.finest("sql="+sql);
      ResultSet rs = st.executeQuery();
      while (rs.next()) {
        ProjectVersion obj = new ProjectVersion(rs.getInt("id"), projectName, 
                rs.getString("name"), rs.getString("descr"), 
                rs.getDate("date_created"), rs.getString("user_created"));
        list.add(obj);
      }
    } finally {
      if (st != null) {st.close();}
    }
    log.finer("beendet");
    return FXCollections.observableArrayList(list);
  }

  public void insertProjectVersion(ProjectVersion obj) throws SQLException {
    log.finer("aufgerufen");
    log.finest("Daten: "+obj);
    String sql = "insert into project_versions "
            + "(name, project_name, descr, date_created, user_created) "
            + "values (?, ?, ?, ?, ?)";
    log.finest("sql="+sql);
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
      stmt.setString(1, obj.getName());
      stmt.setString(2, obj.getProjectName());
      stmt.setString(3, obj.getDescr());
      stmt.setDate(4, new java.sql.Date(new Date().getTime()));
      stmt.setString(5, obj.getUserCreated());
      stmt.executeUpdate();
      ResultSet rs = stmt.getGeneratedKeys();
      rs.next();
      obj.setId(rs.getInt(1));
      conn.commit();
      log.finer("Anzahl eingefuegt=" + stmt.getUpdateCount());
    } finally {
      if (stmt!=null) {stmt.close();}
    }
    log.finer("beendet");
  }

  public void updateProjectVersion(ProjectVersion obj) throws SQLException {
    log.finer("aufgerufen");
    String sql = "update project_versions set name=?, descr=? where id=?";
    log.finest("SQL="+sql+"\nParameter: name="+obj.getName()+
        ", descr="+obj.getDescr()+", id="+obj.getId());
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, obj.getName());
      stmt.setString(2, obj.getDescr());
      stmt.setInt(3, obj.getId());
      stmt.executeUpdate();
      conn.commit();
      log.finer("Anzahl upgedated=" + stmt.getUpdateCount());
    } finally {
      if (stmt!=null) {stmt.close();}
    }
    log.finer("beendet");
  }

  public void deleteProjectVersion(int id) throws SQLException {
    log.finer("aufgerufen");
    String sql = "delete from project_versions where id=?";
    log.finest("SQL="+sql+"\nParameter: id="+id);
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql);
      stmt.setInt(1, id);
      stmt.executeUpdate();
      conn.commit();
      log.finer("Anzahl geloescht="+stmt.getUpdateCount());
    } finally {
      if (stmt!=null) {stmt.close();}
    }
    log.finer("beendet");
  }

  public ObservableList<Role> getRoles() throws SQLException {
    log.finer("aufgerufen");
    List<Role> list = new ArrayList<>();
    Statement stmt = null;
    try {
      stmt = conn.createStatement();
      String sql = "select name, title, admin_jn, projects_jn, requirements_jn,"
              + "testplanning_jn, testrun_jn, reports_jn "
              + "from roles order by name";
      log.finest("sql="+sql);
      ResultSet rs = stmt.executeQuery(sql);
      Role obj;
      while (rs.next()) {
        obj = new Role(rs.getString("name"), rs.getString("title"),
                rs.getString("admin_jn").equals("J"), 
                rs.getString("projects_jn").equals("J"),
                rs.getString("requirements_jn").equals("J"),
                rs.getString("testplanning_jn").equals("J"),
                rs.getString("testrun_jn").equals("J"),
                rs.getString("reports_jn").equals("J")
        );
        list.add(obj);
      }
    } finally {
      if (stmt != null) {stmt.close();}
    }
    log.finer("beendet");
    return FXCollections.observableArrayList(list);
  }
  
  public Role getRoleByName(String name) throws SQLException {
    log.finer("aufgerufen");
    PreparedStatement stmt = null;
    try {
      String sql = "select name, title, admin_jn, projects_jn, requirements_jn,"
              + "testplanning_jn, testrun_jn, reports_jn "
              + "from roles "
              + "where name=? ";
      log.finest("sql="+sql);
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, name);
      ResultSet rs = stmt.executeQuery();
      Role obj = null;
      if (rs.next()) {
        obj = new Role(rs.getString("name"), rs.getString("title"),
                rs.getString("admin_jn").equals("J"), 
                rs.getString("projects_jn").equals("J"),
                rs.getString("requirements_jn").equals("J"),
                rs.getString("testplanning_jn").equals("J"),
                rs.getString("testrun_jn").equals("J"),
                rs.getString("reports_jn").equals("J")
        );
      }
      log.finer("beendet");
      return obj;
    } finally {
      if (stmt != null) {stmt.close();}
    }
  }
  
  public ObservableList<User> getUsers() throws SQLException {
    log.finer("aufgerufen");
    List<User> list = new ArrayList<>();
    Statement stmt = null;
    try {
      stmt = conn.createStatement();
      String sql = "select name, login, fullname, active, date_created, user_created "
              + "from users order by name";
      log.finest("sql="+sql);
      ResultSet rs = stmt.executeQuery(sql);
      User obj;
      while (rs.next()) {
        obj = new User(rs.getString("name"), rs.getString("login"),
                rs.getString("fullname"), rs.getString("active").equals("J"),
                rs.getDate("date_created"), rs.getString("user_created"));
        list.add(obj);
      }
    } finally {
      if (stmt != null) {stmt.close();}
    }
    log.finer("beendet");
    return FXCollections.observableArrayList(list);
  }
  
  public User getUserByName(String name) throws SQLException {
    log.finer("aufgerufen");
    PreparedStatement stmt = null;
    try {
      String sql = "select name, login, fullname, active, date_created, user_created "
              + "from users "
              + "where name=?";
      log.finest("sql="+sql);
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, name);
      ResultSet rs = stmt.executeQuery();
      User obj = null;
      if (rs.next()) {
        obj = new User(rs.getString("name"), rs.getString("login"),
                rs.getString("fullname"), rs.getString("active").equals("J"),
                rs.getDate("date_created"), rs.getString("user_created"));
      }
      log.finer("beendet");
      return obj;
    } finally {
      if (stmt != null) {stmt.close();}
    }
  }
  
  public ObservableList<UserRole> getUserRolesByProjectName(String projectName) throws SQLException {
    log.finer("aufgerufen: projectName="+projectName);
    List<UserRole> list = new ArrayList<>();
    PreparedStatement st = null;
    try {
      String sql = "select id, user_name, role_name "
              + "from user_roles "
              + "where project_name=? "
              + "order by user_name ";
      st = conn.prepareStatement(sql);
      st.setString(1, projectName);
      log.finest("sql="+sql);
      ResultSet rs = st.executeQuery();
      while (rs.next()) {
        Project pro = getProjectById(projectName);
        User user = getUserByName(rs.getString("user_name"));
        Role role = getRoleByName(rs.getString("role_name"));
        UserRole obj = new UserRole(rs.getInt("id"), pro,user,role);
        list.add(obj);
      }
    } finally {
      if (st != null) {st.close();}
    }
    log.finer("beendet");
    return FXCollections.observableArrayList(list);
  }

  public void updateUserRole(UserRole obj) throws SQLException {
    log.finer("aufgerufen");
    String sql = "update user_roles set user_name=?, role_name=? where id=?";
    log.finest("SQL="+sql+"\nParameter: user_name="+obj.getUser().getName()+
        ", role_name="+obj.getRole().getName()+", id="+obj.getId());
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, obj.getUser().getName());
      stmt.setString(2, obj.getRole().getName());
      stmt.setInt(3, obj.getId());
      stmt.executeUpdate();
      conn.commit();
      log.finer("Anzahl upgedated=" + stmt.getUpdateCount());
    } finally {
      if (stmt!=null) {stmt.close();}
    }
  }

  public void insertUserRole(UserRole obj) throws SQLException {
    log.finer("aufgerufen");
    log.finest("Daten: "+obj);
    String sql = "insert into user_roles (project_name, user_name, role_name) "
            + "values (?, ?, ?)";
    log.finest("sql="+sql+", project_name="+obj.getProject().getName()+", user_name="+
            obj.getUser().getName()+", role_name="+obj.getRole().getName());
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
      stmt.setString(1, obj.getProject().getName());
      stmt.setString(2, obj.getUser().getName());
      stmt.setString(3, obj.getRole().getName());
      stmt.executeUpdate();
      ResultSet rs = stmt.getGeneratedKeys();
      rs.next();
      obj.setId(rs.getInt(1));
      conn.commit();
      log.finer("Anzahl eingefuegt=" + stmt.getUpdateCount());
    } finally {
      if (stmt!=null) {stmt.close();}
    }
  }

  public ObservableList<Artefact> getArtefactsByProjectName(String projectName) throws SQLException {
    log.finer("aufgerufen: projectName="+projectName);
    List<Artefact> list = new ArrayList<>();
    PreparedStatement st = null;
    try {
      String sql = "select id, title, project_name, date_created, user_created "
              + "from artefacts "
              + "where project_name=? "
              + "order by title ";
      st = conn.prepareStatement(sql);
      st.setString(1, projectName);
      log.finest("sql="+sql);
      ResultSet rs = st.executeQuery();
      while (rs.next()) {
        Artefact obj = new Artefact(rs.getInt("id"), rs.getString("title"),
             rs.getString("project_name"), rs.getDate("date_created"),
             rs.getString("user_created"));
        list.add(obj);
      }
    } finally {
      if (st != null) {st.close();}
    }
    log.finer("beendet");
    return FXCollections.observableArrayList(list);
  }

  public void insertArtefact(Artefact obj) throws SQLException {
    log.finer("aufgerufen");
    log.finest("Daten: "+obj);
    String sql = "insert into artefacts (project_name, title, date_created, user_created) "
            + "values (?, ?, ?, ?)";
    log.finest("sql="+sql);
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
      stmt.setString(1, obj.getProjectName());
      stmt.setString(2, obj.getTitle());
      stmt.setDate(3, new java.sql.Date(new Date().getTime()));
      stmt.setString(4, obj.getUserCreated());
      stmt.executeUpdate();
      ResultSet rs = stmt.getGeneratedKeys();
      rs.next();
      obj.setId(rs.getInt(1));
      conn.commit();
      log.finer("Anzahl eingefuegt=" + stmt.getUpdateCount());
    } finally {
      if (stmt!=null) {stmt.close();}
    }
  }

  public void updateArtefact(Artefact obj) throws SQLException {
    log.finer("aufgerufen");
    String sql = "update artefacts set title=? where id=?";
    log.finest("SQL="+sql+"\nParameter: title="+obj.getTitle()+", id="+obj.getId());
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, obj.getTitle());
      stmt.setInt(2, obj.getId());
      stmt.executeUpdate();
      conn.commit();
      log.finer("Anzahl upgedated=" + stmt.getUpdateCount());
    } finally {
      if (stmt!=null) {stmt.close();}
    }
  }

  public ObservableList<Glossary> getGlossaryByProjectName(String projectName) throws SQLException {
    log.finer("aufgerufen: projectName="+projectName);
    List<Glossary> list = new ArrayList<>();
    PreparedStatement st = null;
    try {
      String sql = "select id, name, project_name, synonyms, descr, date_created, user_created "
              + "from glossary "
              + "where project_name=? "
              + "order by name ";
      st = conn.prepareStatement(sql);
      st.setString(1, projectName);
      log.finest("sql="+sql);
      ResultSet rs = st.executeQuery();
      while (rs.next()) {
        Glossary obj = new Glossary(rs.getInt("id"), rs.getString("project_name"), 
             rs.getString("name"), rs.getString("synonyms"), rs.getString("descr"),
             rs.getDate("date_created"), rs.getString("user_created"));
        list.add(obj);
      }
    } finally {
      if (st != null) {st.close();}
    }
    log.finer("beendet");
    return FXCollections.observableArrayList(list);
  }

  public void insertGlossary(Glossary obj) throws SQLException {
    log.finer("aufgerufen");
    log.finest("Daten: "+obj);
    String sql = "insert into glossary (project_name, name, synonyms, descr, date_created, user_created) "
            + "values (?, ?, ?, ?, ?, ?)";
    log.finest("sql="+sql);
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
      stmt.setString(1, obj.getProjectName());
      stmt.setString(2, obj.getName());
      stmt.setString(3, obj.getSynonyms());
      stmt.setString(4, obj.getDescr());
      stmt.setDate(5, new java.sql.Date(new Date().getTime()));
      stmt.setString(6, obj.getUserCreated());
      stmt.executeUpdate();
      ResultSet rs = stmt.getGeneratedKeys();
      rs.next();
      obj.setId(rs.getInt(1));
      conn.commit();
      log.finer("Anzahl eingefuegt=" + stmt.getUpdateCount());
    } finally {
      if (stmt!=null) {stmt.close();}
    }
  }

  public void updateGlossary(Glossary obj) throws SQLException {
    log.finer("aufgerufen");
    String sql = "update glossary set name=?, synonyms=?, descr=? where id=?";
    log.finest("SQL="+sql+"\nParameter: title="+obj.getName()+", synonyms="+
            obj.getSynonyms()+", descr="+obj.getDescr()+", id="+obj.getId());
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, obj.getName());
      stmt.setString(2, obj.getSynonyms());
      stmt.setString(3, obj.getDescr());
      stmt.setInt(4, obj.getId());
      stmt.executeUpdate();
      conn.commit();
      log.finer("Anzahl upgedated=" + stmt.getUpdateCount());
    } finally {
      if (stmt!=null) {stmt.close();}
    }
  }
  
  public Artefact getArtefact(Integer id) throws SQLException {
    log.finer("aufgerufen");
    Artefact obj = null;
    PreparedStatement ps = null;
    try {
      String sql = "select id, title, project_name, date_created, user_created "
              + "from artefacts where id=?";
      log.finest("sql="+sql);
      ps = conn.prepareStatement(sql);
      ps.setInt(1, id);
      ResultSet rs = ps.executeQuery();
      if (rs.next()) {
        obj = new Artefact(id, rs.getString("title"), rs.getString("project_name"), 
                rs.getDate("date_created"), rs.getString("user_created"));
      }
    } finally {
      if (ps != null) {ps.close();}
    }
    log.finer("beendet");
    return obj;
  }
  
  public Category getCategory(String name) throws SQLException {
    log.finer("aufgerufen");
    Category obj = null;
    PreparedStatement ps = null;
    try {
      String sql = "select name, descr "
              + "from categories where name=?";
      log.finest("sql="+sql);
      ps = conn.prepareStatement(sql);
      ps.setString(1, name);
      ResultSet rs = ps.executeQuery();
      if (rs.next()) {
        obj = new Category(name, rs.getString("descr"));
      }
    } finally {
      if (ps != null) {ps.close();}
    }
    log.finer("beendet");
    return obj;
  }
  
  public ProjectVersion getProjectVersionByName(String projectName, String versionName) throws SQLException {
    log.finer("aufgerufen");
    ProjectVersion obj = null;
    PreparedStatement ps = null;
    try {
      String sql = "select id, name, project_name, descr, date_created, user_created "
              + "from project_versions "
              + "where project_name=? and name=?";
      log.finest("sql="+sql);
      ps = conn.prepareStatement(sql);
      ps.setString(1, projectName);
      ps.setString(2, versionName);
      ResultSet rs = ps.executeQuery();
      if (rs.next()) {
        obj = new ProjectVersion(rs.getInt("id"), projectName, versionName, 
                rs.getString("descr"), rs.getDate("date_created"), rs.getString("user_created"));
      }
    } finally {
      if (ps != null) {ps.close();}
    }
    log.finer("beendet");
    return obj;
  }

  public ObservableList<Category> getCategories() throws SQLException {
    log.finer("aufgerufen");
    List<Category> list = new ArrayList<>();
    PreparedStatement ps = null;
    try {
      String sql = "select name, descr "
              + "from categories order by name";
      log.finest("sql="+sql);
      ps = conn.prepareStatement(sql);
      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
        Category obj = new Category(rs.getString("name"), rs.getString("descr"));
        list.add(obj);
      }
    } finally {
      if (ps != null) {ps.close();}
    }
    log.finer("beendet");
    return FXCollections.observableArrayList(list);
  }
  
  public ObservableList<Testpack> getTestpacksForProject(String projectName) throws SQLException {
    log.finer("aufgerufen: projectName="+projectName);
    List<Testpack> list = new ArrayList<>();
    PreparedStatement st = null;
    try {
      String sql = "select id, kind, method, descr, date_created, user_created "
              + "from testpacks "
              + "where project_name=? "
              + "order by id ";
      st = conn.prepareStatement(sql);
      st.setString(1, projectName);
      log.finest("sql="+sql);
      ResultSet rs = st.executeQuery();
      while (rs.next()) {
        Testpack obj = new Testpack(rs.getInt("id"), projectName, 
                Testpack.getKind(rs.getString("kind")), 
                Testpack.getMethod(rs.getString("method")), rs.getString("descr"), 
                rs.getDate("date_created"), rs.getString("user_created"));
        list.add(obj);
      }
    } finally {
      if (st != null) {st.close();}
    }
    log.finer("beendet");
    return FXCollections.observableArrayList(list);
  }

  private List<String> getVersionList(ObservableList<ProjectVersion> list) {
    List<String> ret = new ArrayList<>();
    for (ProjectVersion obj: list) {
      ret.add(obj.getName());
    }
    return ret;
  }

  private List<String> getArtefactList(ObservableList<Artefact> list) {
    List<String> ret = new ArrayList<>();
    for (Artefact obj: list) {
      ret.add(obj.getTitle());
    }
    return ret;
  }

  private List<GlossaryX> getGlossaryList(ObservableList<Glossary> list) {
    List<GlossaryX> ret = new ArrayList<>();
    for (Glossary obj: list) {
      ret.add(new GlossaryX(obj.getName(), obj.getSynonyms(), obj.getDescr()));
    }
    return ret;
  }

  public void setRequirementReleased(int id) throws SQLException {
    log.finer("aufgerufen: id="+id);
    String sql = "update requirements set state='released' where id=?";
    log.finest("sql="+sql);
    PreparedStatement st = null;
    try {
      st = conn.prepareStatement(sql);
      st.setInt(1, id);
      st.executeUpdate();
      log.finer("Datensatz updated");
    } finally {
      if (st!=null) {st.close();}
    }
  }
  
  
  
}